﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_SetupLeader]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_SetupLeader];
GO
CREATE PROCEDURE [dbo].[sproc_SetupLeader]
    @TeamID int,
    @StaffIDS nvarchar(3000),
    @Unique bit=0
/*

============================================================
功能:    设置组长
参数:
    @TeamID int            :    组（类）ID
    @StaffIDS nvarchar(3000)        :    设置为组长的ID集合
    @RightCode int =10,        :    组长的权限包ID（代号）
    @Unique bit=0            :    组长是否为唯一

============================================================

*/
AS
DECLARE @SQL nvarchar(4000)
DECLARE @Receiver nvarchar(4000)
DECLARE @Leader nvarchar(2000)
CREATE TABLE #T(staff_id int)

--把组长列表转化为临时表
EXEC ('INSERT INTO #T SELECT staff_id FROM uds_staff WHERE staff_id in (' + @staffids + ')')


--添加组长
IF @Unique=0 
BEGIN
    --删除原来组长
    UPDATE uds_staff_in_team 
        SET member_type = 1
        WHERE team_id = @TeamID
    UPDATE uds_staff_in_team
        SET member_type = 2
        WHERE staff_id = (SELECT TOP 1 staff_id FROM #T)
          and team_id = @TeamID
END
ELSE    
    UPDATE uds_staff_in_team
        SET member_type = 2
        WHERE staff_id = (SELECT staff_id FROM #T)
          and team_id = @TeamID

/*
--得到组长变更的邮件接收者
SET @Receiver =''
SELECT @Receiver = @Receiver +  a.staff_name + ','
    FROM     uds_staff a,
        uds_staff_in_team b 
    WHERE         a.staff_id = b.staff_id 
        and b.Team_ID = @TeamID 
        and a.dimission =0
IF len(@Receiver)>0 
    SET @Receiver = Left(@Receiver,len(@Receiver)-1)
SET @Leader = ''
SELECT @Leader = @Leader + staff_name + ',' 
    FROM 
        uds_staff 
    WHERE staff_id in (SELECT staff_id FROM #T)
IF len(@Leader)>0 
    SET @Leader = Left(@Leader,len(@Leader)-1)

PRINT @Receiver
PRINT @Leader


SET @Leader = '这是系统邮件，现在组长变更为' + @Leader

-- 给每个组成员发送组长变更邮件              
Exec sproc_MySendMail @TeamID,@Receiver,'','','系统邮件','组长变更', @Leader,0,0

*/